﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Globalization;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_F5YoyakuPlan : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
                
        private bool calculated;
        private bool loadKoseiUnion;
        private int state;

        private List<int> change_row;
        
        public panel_F5YoyakuPlan()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);

            this.change_row = new List<int>();

            this.state = 0;
                        
            this.loadKoseiUnion = false;
            this.calculated = false;

            this.buttonBack.Visible = false;
            this.buttonExportExcel.Visible = false;
            this.buttonLate.Visible = false;
            this.buttonShortage.Visible = false;
            this.buttonYoyaku.Visible = false;
            this.buttonRemain.Visible = false;

            if (MessageBox.Show("Do you want to load new data from Densan?", "Confirm load data", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                MessageBox.Show("This will take some minutes. Please patient!");
                
                CalculateOrderList();
                
                ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
                ModuleWaitDialog.SetWaitDialogCaption("Loading data...");
                LoadNewData();
                ModuleWaitDialog.CloseWaitDialog();
            }            

            ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
            ModuleWaitDialog.SetWaitDialogCaption("Loading data...");
            
            LoadData();

            ModuleWaitDialog.CloseWaitDialog();

            this.F5YoyakuPlan.PopupMenuShowing += F5YoyakuPlan_PopupMenuShowing;
            this.f5YoyakuPlanContext.ItemClicked += f5YoyakuPlanContext_ItemClicked;
            F5YoyakuPlan.CellValueChanged += F5YoyakuPlan_CellValueChanged;
        }

        void F5YoyakuPlan_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
        {
            F5YoyakuPlan.PostEditor();
            F5YoyakuPlan.UpdateCurrentRow();
            change_row.Add(F5YoyakuPlan.FocusedRowHandle);
        }

        //////////////////////////////////////////////
        /// Load Kosei Union to local database
        //////////////////////////////////////////////
        private void loadKoseiUnionAS400()
        {
            ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
            ModuleWaitDialog.SetWaitDialogCaption("Updateing Kosei table...");
            try
            {
                myConnection.Open();
                SqlCommand sql = myConnection.CreateCommand();
                sql.CommandText = "DELETE [222_KoseiUnion]";
                sql.CommandTimeout = 30000;
                sql.ExecuteNonQuery();
                sql.Dispose();

                sql = myConnection.CreateCommand();
                sql.CommandText = "INSERT INTO [222_KoseiUnion] ([COMPID], [PARTNO], [QUANTITY], [PARTTYPE], [NOTE])"
                    + " SELECT [COMPID], [PARTNO], [QUANTITY], [PARTTYPE], [NOTE] FROM [VKosei]";
                sql.CommandTimeout = 30000;
                sql.ExecuteNonQuery();
                sql.Dispose();
                myConnection.Close();
                loadKoseiUnion = true;
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                ModuleWaitDialog.CloseWaitDialog();
            }
            ModuleWaitDialog.CloseWaitDialog();
        }

        ////////////////////////////////////////////////
        /// Calculate Structure for Order List
        ///////////////////////////////////////////////
        private void CalculateOrderList()
        {
            if (!loadKoseiUnion)
            {
                if (MessageBox.Show("Do you want to Update Kosei Table?", "Confirm update kosei", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    loadKoseiUnionAS400();
                }
            }
            bool pass = true;
            // Clear old data
            ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
            ModuleWaitDialog.SetWaitDialogCaption("Clear Old Order list...");            
            try
            {
                myConnection.Open();
                SqlCommand sql = myConnection.CreateCommand();
                sql.CommandText = "DELETE [231_F5YoyakuOrder]";
                sql.ExecuteNonQuery();
                sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                pass = false;
                ModuleWaitDialog.CloseWaitDialog();
            }
            ModuleWaitDialog.CloseWaitDialog();

            if (pass)
            {
                DataTable FinalOrder = new DataTable();
                FinalOrder.Columns.Add("OrderNo", typeof(string));
                FinalOrder.Columns.Add("PartNo", typeof(string));
                FinalOrder.Columns.Add("Quantity", typeof(float));
                FinalOrder.Columns.Add("HopeDate", typeof(string));
                FinalOrder.Columns.Add("ArrivalDate", typeof(string));

                DataTable CurrentOrder = new DataTable();
                CurrentOrder.Columns.Add("OrderNo", typeof(string));
                CurrentOrder.Columns.Add("PartNo", typeof(string));
                CurrentOrder.Columns.Add("Quantity", typeof(float));
                CurrentOrder.Columns.Add("HopeDate", typeof(string));
                CurrentOrder.Columns.Add("ArrivalDate", typeof(string));

                DataTable NextOrder = new DataTable();
                NextOrder.Columns.Add("OrderNo", typeof(string));
                NextOrder.Columns.Add("PartNo", typeof(string));
                NextOrder.Columns.Add("Quantity", typeof(float));
                NextOrder.Columns.Add("HopeDate", typeof(string));
                NextOrder.Columns.Add("ArrivalDate", typeof(string));

                DataTable KoseiTable = new DataTable();
                KoseiTable.Columns.Add("CompID", typeof(string));
                KoseiTable.Columns.Add("PartNo", typeof(string));
                KoseiTable.Columns.Add("KoseiQuantity", typeof(float));

                // Load data to calculate
                ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
                ModuleWaitDialog.SetWaitDialogCaption("Loading New Order list...");                
                try
                {
                    myConnection.Open();
                    SqlCommand sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "SELECT [ORDERNO], [PARTPURCHASE], [TOTALQUANTITY], [EXPECTDATE], [ARRIVALDATE]"
                        + " FROM [VF5OrderFinal] WHERE [PARTPURTYPE]='M'";
                    sql_cmd.CommandTimeout = 30000;
                    SqlDataReader dataReader = sql_cmd.ExecuteReader();
                    while (dataReader.Read())
                    {
                        DataRow order_row = FinalOrder.NewRow();
                        order_row["OrderNo"] = dataReader["ORDERNO"].ToString().Trim();
                        order_row["PartNo"] = dataReader["PARTPURCHASE"].ToString().Trim();
                        float qty = 0;
                        float.TryParse(dataReader["TOTALQUANTITY"].ToString().Trim(), out qty);
                        order_row["Quantity"] = qty;
                        order_row["HopeDate"] = dataReader["EXPECTDATE"].ToString().Trim();
                        order_row["ArrivalDate"] = dataReader["ARRIVALDATE"].ToString().Trim();
                        FinalOrder.Rows.Add(order_row);
                    }
                    dataReader.Close();
                    sql_cmd.Dispose();
                    myConnection.Close();
                }
                catch
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    pass = false;
                }
                ModuleWaitDialog.CloseWaitDialog();

                if (pass)
                {
                    // Calculate structure
                    ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
                    ModuleWaitDialog.SetWaitDialogCaption("Calculate structure for New Order list...");
                    ModuleWaitDialog.CloseWaitDialog();

                    try
                    {
                        CurrentOrder = FinalOrder.Copy();

                        myConnection.Open();
                        SqlCommand cmd;
                        SqlDataReader dataReader;
                        int level = 1;
                        while (CurrentOrder.Rows.Count > 0)
                        {
                            ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
                            ModuleWaitDialog.SetWaitDialogCaption("Calculating level " + level + ": " + CurrentOrder.Rows.Count + " comps...");                            
                            NextOrder.Rows.Clear();
                            string[] comp_array = new string[CurrentOrder.Rows.Count];
                            int i = 0;
                            foreach (DataRow current_row in CurrentOrder.Rows)
                            {
                                comp_array[i] = "'" + current_row["PartNo"].ToString().Trim() + "'";
                                i++;
                            }

                            string comp_list = null;
                            comp_list = string.Join(",", comp_array);

                            // Load Kosei table for current level
                            cmd = myConnection.CreateCommand();
                            cmd.CommandText = "SELECT COMPID, PARTNO, QUANTITY FROM [222_KoseiUnion]"
                                + " WHERE COMPID IN (" + comp_list + ") AND PARTTYPE='M'";                            
                            cmd.CommandTimeout = 30000;
                            dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                DataRow new_kosei_row = KoseiTable.NewRow();
                                new_kosei_row["CompID"] = dataReader["COMPID"].ToString().Trim();
                                new_kosei_row["PartNo"] = dataReader["PARTNO"].ToString().Trim();
                                float koseiqty = 0;
                                float.TryParse(dataReader["QUANTITY"].ToString().Trim(), out koseiqty);
                                new_kosei_row["KoseiQuantity"] = koseiqty;
                                KoseiTable.Rows.Add(new_kosei_row);
                            }
                            dataReader.Close();
                            cmd.Dispose();

                            // Calculate structure
                            foreach (DataRow current_row in CurrentOrder.Rows)
                            {
                                DataRow[] kosei_rows = KoseiTable.Select("CompID='" + current_row["PartNo"].ToString().Trim() + "'");
                                foreach (DataRow kosei_row in kosei_rows)
                                {
                                    DataRow new_final_row = FinalOrder.NewRow();
                                    DataRow new_next_row = NextOrder.NewRow();
                                    new_final_row["OrderNo"] = current_row["OrderNo"].ToString();
                                    new_next_row["OrderNo"] = current_row["OrderNo"].ToString();
                                    new_final_row["PartNo"] = kosei_row["PartNo"].ToString();
                                    new_next_row["PartNo"] = kosei_row["PartNo"].ToString();
                                    float koseiqty = 0;
                                    float.TryParse(kosei_row["KoseiQuantity"].ToString().Trim(), out koseiqty);
                                    float parentsqty = 0;
                                    float.TryParse(current_row["Quantity"].ToString(), out parentsqty);
                                    new_final_row["Quantity"] = koseiqty * parentsqty;
                                    new_next_row["Quantity"] = koseiqty * parentsqty;
                                    new_final_row["HopeDate"] = current_row["HopeDate"].ToString();
                                    new_next_row["HopeDate"] = current_row["HopeDate"].ToString();
                                    new_final_row["ArrivalDate"] = current_row["ArrivalDate"].ToString();
                                    new_next_row["ArrivalDate"] = current_row["ArrivalDate"].ToString();
                                    FinalOrder.Rows.Add(new_final_row);
                                    NextOrder.Rows.Add(new_next_row);
                                }
                            }
                            KoseiTable.Rows.Clear();
                            CurrentOrder.Rows.Clear();
                            CurrentOrder = NextOrder.Copy();
                            ModuleWaitDialog.CloseWaitDialog();
                            level++;
                        }

                        // Write to database
                        foreach (DataRow final_row in FinalOrder.Rows)
                        {
                            cmd = myConnection.CreateCommand();
                            cmd.CommandText = "INSERT INTO [231_F5YoyakuOrder] (OrderNo, PartNo, TotalQuantity, HopeDate, ArrivalDate)"
                                + " VALUES ('" + final_row["OrderNo"].ToString() + "',"
                                + " N'" + final_row["PartNo"].ToString() + "',"
                                + " " + final_row["Quantity"].ToString() + ","
                                + " '" + final_row["HopeDate"].ToString() + "',"
                                + " '" + final_row["ArrivalDate"].ToString() + "')";
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                        }

                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        ModuleWaitDialog.CloseWaitDialog();
                        MessageBox.Show("Cannot calculate order: " + ex.Message);
                    }
                }
                else
                {
                    MessageBox.Show("Cannot load data to calculate order");
                }
            }
            else
            {
                MessageBox.Show("Cannot clear old data");
            }
        }
        

        /////////////////////////////////////////////
        /// Load Plan data from AS400
        /////////////////////////////////////////////
        private void LoadNewData()
        {            
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                
                // Cleare old data
                sql_cmd.CommandText = "DELETE [231_F5YoyakuPlan]";
                sql_cmd.ExecuteNonQuery();
                sql_cmd.Dispose();

                // Get new data
                sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "INSERT INTO [231_F5YoyakuPlan] ([OrderNo], [RobotNo], [RobotQuantity],"
                    + " [HopeDate], [ArrivalDate], [PartDate], FixedDate)"
                    + " SELECT [ORDERNO], [PARTNO], [TOTALQUANTITY], [EXPECTDATE],"
                    + " CASE WHEN [ARRIVALDATE] = '0' THEN [EXPECTDATE] ELSE [ARRIVALDATE] END  AS [ARRIVALDATE],"
                    + " CASE WHEN [ARRIVALDATE] = '0' THEN [EXPECTDATE] ELSE [ARRIVALDATE] END  AS [PARTDATE],"
                    + " CASE WHEN [ARRIVALDATE] = '0' THEN 0 ELSE 1 END AS [FIXEDATE] FROM [VF5Plan]";
                sql_cmd.CommandTimeout = 30000;
                sql_cmd.ExecuteNonQuery();
                sql_cmd.Dispose();

                sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "UPDATE [231_F5YoyakuPlan]"
                    + " SET PartDate=CONVERT(NVARCHAR(20), DATEADD(dd,-15,CONVERT(DATETIME, [ArrivalDate], 112)), 112)";
                sql_cmd.ExecuteNonQuery();
                sql_cmd.Dispose();
                myConnection.Close();
                loadKoseiUnion = false;
                calculated = false;
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get new data from AS400");
            }
        }

        /////////////////////////////////////////////
        /// Load Calculate structure with Kosei
        /////////////////////////////////////////////
        private void LoadKoseiData()
        {
            ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
            ModuleWaitDialog.SetWaitDialogCaption("Calculating structure for plan list...");
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();

                // Cleare old data
                sql_cmd.CommandText = "DELETE [231_F5YoyakuPlanKosei]";
                sql_cmd.ExecuteNonQuery();
                sql_cmd.Dispose();

                // Get new data
                sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "INSERT INTO [231_F5YoyakuPlanKosei] ([OrderNo], [RobotNo], [PartNo], [RobotQuantity],"
                    + " [KoseiQuantity], [TotalPartQuantity], [HopeDate], [ArrivalDate], [FixedDate], [PartDate])"
                    + " SELECT [OrderNo], [RobotNo], [PARTNO], [RobotQuantity], [QUANTITY],"
                    + " [RobotQuantity] * [QUANTITY] AS TOTALPARTQUANTITY, [HopeDate], [ArrivalDate], [FixedDate], [PartDate]"
                    + " FROM [VF5PlanFinal]";
                sql_cmd.CommandTimeout = 30000;
                sql_cmd.ExecuteNonQuery();
                sql_cmd.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot canculate structure for plan list.");
            }
            ModuleWaitDialog.CloseWaitDialog();
        }


        void f5YoyakuPlanContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.f5YoyakuPlanContext.Hide();
            DataRow row = F5YoyakuPlan.GetFocusedDataRow();
            switch (e.ClickedItem.Name)
            {   
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }


        void F5YoyakuPlan_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.f5YoyakuPlanContext.Show(MousePosition);
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT [ID], [OrderNo], [RobotNo], [RobotQuantity], [HopeDate], [ArrivalDate], [PartDate], FixedDate"
                + " FROM [231_F5YoyakuPlan]";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridF5YoyakuPlan.DataSource = sourceDataSet.Tables[0];
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(string));
                newTable.Columns.Add("OrderNo", typeof(string));
                newTable.Columns.Add("RobotNo", typeof(string));
                newTable.Columns.Add("RobotQuantity", typeof(float));
                newTable.Columns.Add("HopeDate", typeof(string));
                newTable.Columns.Add("ArrivalDate", typeof(string));
                newTable.Columns.Add("PartDate", typeof(string));
                newTable.Columns.Add("FixedDate", typeof(bool));
                gridF5YoyakuPlan.DataSource = newTable;
            }
            F5YoyakuPlan.Columns[0].Caption = "ID";
            F5YoyakuPlan.Columns[1].Caption = "Order No.";
            F5YoyakuPlan.Columns[2].Caption = "Robot No.";
            F5YoyakuPlan.Columns[3].Caption = "Robot Quantity";
            F5YoyakuPlan.Columns[4].Caption = "Hope Date";
            F5YoyakuPlan.Columns[5].Caption = "Arrival Date";
            F5YoyakuPlan.Columns[6].Caption = "Part Date";
            F5YoyakuPlan.Columns[7].Caption = "Fixed Date";
            F5YoyakuPlan.Columns[0].Visible = false;
            F5YoyakuPlan.BestFitColumns();

            F5YoyakuPlan.Columns[0].OptionsColumn.AllowEdit = false;
            F5YoyakuPlan.Columns[1].OptionsColumn.AllowEdit = false;
            F5YoyakuPlan.Columns[2].OptionsColumn.AllowEdit = false;
            F5YoyakuPlan.Columns[3].OptionsColumn.AllowEdit = false;
            F5YoyakuPlan.Columns[4].OptionsColumn.AllowEdit = false;
            F5YoyakuPlan.Columns[5].OptionsColumn.AllowEdit = false;
            F5YoyakuPlan.Columns[7].OptionsColumn.AllowEdit = false;            
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();            
        }


        private void buttonEdit_Click(object sender, EventArgs e)
        {            
             
        }

        private void buttonExport_Click(object sender, EventArgs e)
        {
            Cursor currentCursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                SaveFileDialog saveFDlg = new SaveFileDialog();
                saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                saveFDlg.Filter = "Microsoft Office Excel|*.xls";
                saveFDlg.Title = "Save As";
                if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                {
                    if (!string.IsNullOrEmpty(saveFDlg.FileName))
                    {
                        F5YoyakuPlan.OptionsPrint.PrintDetails = true;
                        F5YoyakuPlan.OptionsPrint.ExpandAllDetails = true;
                        F5YoyakuPlan.OptionsPrint.AutoWidth = true;
                        F5YoyakuPlan.ExportToXls(saveFDlg.FileName);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Warning!");
            }
            finally
            {
                Cursor.Current = currentCursor;
            }
        }

        private void buttonGetNew_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Do you want to get new data from AS 400?", "Confirm load data", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                CalculateOrderList();

                ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
                ModuleWaitDialog.SetWaitDialogCaption("Loading data...");                
                LoadNewData();
                LoadData();
                ModuleWaitDialog.CloseWaitDialog();
            }
        }

        private void buttonHave_Click(object sender, EventArgs e)
        {
            if (!loadKoseiUnion)
            {
                if (MessageBox.Show("Do you want to update Kosei Table?", "Confirm update Kosei", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    loadKoseiUnionAS400();
                }                
            }

            LoadKoseiData();

            frm_View_F5YoyakuSupply view_form = new frm_View_F5YoyakuSupply();
            bool isloaded = false;
            ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
            ModuleWaitDialog.SetWaitDialogCaption("Loading Import List...");
            isloaded = view_form.LoadData();
            ModuleWaitDialog.CloseWaitDialog();
            if (isloaded)
            {
                view_form.ShowDialog();
            }
            else
            {
                MessageBox.Show("There is no data for supply list");
            }
        }

        private void buttonCalculate_Click(object sender, EventArgs e)
        {
            string update = null;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql;
                foreach (int i in change_row)
                {
                    DataRow row = F5YoyakuPlan.GetDataRow(i);
                    cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "UPDATE [231_F5YoyakuPlan]"
                        + " SET PartDate='" + row[6].ToString() + "'"
                        + " WHERE ID=" + row[0].ToString();
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                }
                myConnection.Close();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                update = ex.Message;
                //MessageBox.Show("Cannot update database");
            }

            if (string.IsNullOrEmpty(update))
            {
                if (!calculated)
                {
                    CalculateYoyaku();
                    calculated = true;
                    ShowResult();
                }
                else
                {
                    if (MessageBox.Show("Do you want to calculate new data?", "Confirm re-calculate", MessageBoxButtons.YesNo) == DialogResult.Yes)
                    {
                        CalculateYoyaku();
                        ShowResult();
                    }
                    else
                    {
                        ShowResult();
                    }
                }
            }
            else
            {
                MessageBox.Show(update);
            }
        }


        private void CalculateYoyaku()
        {            
            // Delete old Yoyaku
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();

                // Cleare old data
                sql_cmd.CommandText = "DELETE [231_Yoyaku]";
                sql_cmd.ExecuteNonQuery();
                sql_cmd.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
            }

            bool isload = true;
            if (!loadKoseiUnion)
            {
                if (MessageBox.Show("Do you want to update Kosei Table?", "Confirm update Kosei", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    loadKoseiUnionAS400();                    
                }                
            }

            LoadKoseiData();

            DataTable TotalNeedTable = new DataTable();
            TotalNeedTable.Columns.Add("OrderNo", typeof(string));
            TotalNeedTable.Columns.Add("RobotNo", typeof(string));
            TotalNeedTable.Columns.Add("PartNo", typeof(string));
            TotalNeedTable.Columns.Add("RobotQuantity", typeof(float));
            TotalNeedTable.Columns.Add("KoseiQuantity", typeof(float));
            TotalNeedTable.Columns.Add("TotalPartQuantity", typeof(float));
            TotalNeedTable.Columns.Add("HopeDate", typeof(string));
            TotalNeedTable.Columns.Add("ArrivalDate", typeof(string));
            TotalNeedTable.Columns.Add("FixedDate", typeof(int));
            TotalNeedTable.Columns.Add("PartDate", typeof(string));

            DataTable TotalHave = new DataTable();
            TotalHave.Columns.Add("OrderNo", typeof(string));
            TotalHave.Columns.Add("PartNo", typeof(string));
            TotalHave.Columns.Add("TotalQuantity", typeof(string));
            TotalHave.Columns.Add("DeliveryDate", typeof(string));
            TotalHave.Columns.Add("ArrivalDate", typeof(string));

            DataTable YoyakuTable = new DataTable();
            YoyakuTable.Columns.Add("OrderNo", typeof(string));
            YoyakuTable.Columns.Add("RobotNo", typeof(string));
            YoyakuTable.Columns.Add("RobotQuantity", typeof(float));
            YoyakuTable.Columns.Add("DeadLine", typeof(string));
            YoyakuTable.Columns.Add("PartNo", typeof(string));
            YoyakuTable.Columns.Add("NeedQuantity", typeof(float));
            YoyakuTable.Columns.Add("FixedDate", typeof(int));
            YoyakuTable.Columns.Add("ImportOrderNo", typeof(string));
            YoyakuTable.Columns.Add("SupplyQuantity", typeof(float));
            YoyakuTable.Columns.Add("DeliveryDate", typeof(string));
            YoyakuTable.Columns.Add("ArrivalDate", typeof(string));
            YoyakuTable.Columns.Add("PartDate", typeof(string));

            ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
            ModuleWaitDialog.SetWaitDialogCaption("Loading Data...");
            // Load data to table
            try
            {
                SqlCommand sql_cmd;
                SqlDataReader dataReader;
                
                myConnection.Open();              
                

                // Load Total need part
                sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT * FROM [231_F5YoyakuPlanKosei] ORDER BY FixedDate DESC, ArrivalDate ASC";
                dataReader = sql_cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    DataRow need_row = TotalNeedTable.NewRow();
                    need_row["OrderNo"] = dataReader["OrderNo"].ToString().Trim();
                    need_row["RobotNo"] = dataReader["RobotNo"].ToString().Trim();
                    need_row["PartNo"] = dataReader["PartNo"].ToString().Trim();
                    float robotqty = 0;
                    if (float.TryParse(dataReader["RobotQuantity"].ToString().Trim(), out robotqty))
                    {
                        need_row["RobotQuantity"] = robotqty;
                    }
                    else
                    {
                        need_row["RobotQuantity"] = 0;
                    }
                    float koseiqty = 0;
                    if (float.TryParse(dataReader["KoseiQuantity"].ToString().Trim(), out koseiqty))
                    {
                        need_row["KoseiQuantity"] = koseiqty;
                    }
                    else
                    {
                        need_row["KoseiQuantity"] = 0;
                    }
                    float totalqty = 0;
                    if (float.TryParse(dataReader["TotalPartQuantity"].ToString().Trim(), out totalqty))
                    {
                        need_row["TotalPartQuantity"] = totalqty;
                    }
                    else
                    {
                        need_row["TotalPartQuantity"] = 0;
                    }
                    need_row["HopeDate"] = dataReader["HopeDate"].ToString().Trim();
                    need_row["ArrivalDate"] = dataReader["ArrivalDate"].ToString().Trim();
                    if ((bool)dataReader["FixedDate"])
                    {
                        need_row["FixedDate"] = 1;
                    }
                    else
                    {
                        need_row["FixedDate"] = 0;
                    }
                    need_row["PartDate"] = dataReader["PartDate"].ToString().Trim();
                    TotalNeedTable.Rows.Add(need_row);
                }
                dataReader.Close();
                sql_cmd.Dispose();

                // Load Total have part
                sql_cmd.CommandText = "SELECT [ORDERNO], [PARTNO], [TOTALQUANTITY], [DELIVERYDATE], [ARRIVALDATE]"
                    + " FROM [VF5TotalHave]"
                    + " WHERE [PARTNO] IN (SELECT PartNo FROM [231_F5YoyakuPlanKosei])"
                    + " ORDER BY [ARRIVALDATE] ASC";
                dataReader = sql_cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    DataRow have_row = TotalHave.NewRow();
                    have_row["OrderNo"] = dataReader["ORDERNO"].ToString().Trim();
                    have_row["PartNo"] = dataReader["PARTNO"].ToString().Trim();
                    float qty = 0;
                    if (float.TryParse(dataReader["TOTALQUANTITY"].ToString().Trim(), out qty))
                    {
                        have_row["TotalQuantity"] = qty;
                    }
                    else
                    {
                        have_row["TotalQuantity"] = 0;
                    }
                    have_row["DeliveryDate"] = dataReader["DELIVERYDATE"].ToString().Trim();
                    have_row["ArrivalDate"] = dataReader["ARRIVALDATE"].ToString().Trim();
                    TotalHave.Rows.Add(have_row);
                }
                dataReader.Close(); 
                sql_cmd.Dispose();
                myConnection.Close();
            }
            catch //(Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                //MessageBox.Show(ex.Message);
                isload = false;
            }
            ModuleWaitDialog.CloseWaitDialog();

            
            // Calculate yoyaku
            if (isload)
            {
                try
                {
                    int totalrecords = TotalNeedTable.Rows.Count;
                    int count = 1;
                    ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");

                    foreach (DataRow need_part in TotalNeedTable.Rows)
                    {
                        ModuleWaitDialog.SetWaitDialogCaption("Yoyaku Calculating " + count + " of " + totalrecords + "...");
                        count++;
                        float getqty = 0;
                        float totalneed = 0;
                        float.TryParse(need_part["TotalPartQuantity"].ToString(), out totalneed);
                        if (totalneed > 0)
                        {                            
                            DataRow[] have_parts = TotalHave.Select("PartNo='" + need_part["PartNo"].ToString().Trim() + "'");
                            if (have_parts.Length > 0)
                            {
                                foreach (DataRow have_part in have_parts)
                                {
                                    if (totalneed > 0)
                                    {
                                        float totalhave = 0;
                                        float.TryParse(have_part["TotalQuantity"].ToString(), out totalhave);
                                        if (totalhave >= totalneed)
                                        {
                                            getqty = totalneed;
                                            totalneed = 0;
                                            totalhave = totalhave - getqty;

                                            // Add to Yoyaku
                                            DataRow yoyaku_row = YoyakuTable.NewRow();
                                            yoyaku_row["OrderNo"] = need_part["OrderNo"].ToString();
                                            yoyaku_row["RobotNo"] = need_part["RobotNo"].ToString();
                                            float robotqty = 0;
                                            if (float.TryParse(need_part["RobotQuantity"].ToString().Trim(), out robotqty))
                                            {
                                                yoyaku_row["RobotQuantity"] = robotqty;
                                            }
                                            else
                                            {
                                                yoyaku_row["RobotQuantity"] = 0;
                                            }
                                            yoyaku_row["DeadLine"] = need_part["ArrivalDate"].ToString();
                                            yoyaku_row["PartNo"] = need_part["PartNo"].ToString();
                                            yoyaku_row["NeedQuantity"] = getqty;
                                            int fix = 0;
                                            if (Int32.TryParse(need_part["FixedDate"].ToString(), out fix))
                                            {
                                                yoyaku_row["FixedDate"] = fix;
                                            }
                                            else
                                            {
                                                yoyaku_row["FixedDate"] = 0;
                                            }
                                            yoyaku_row["ImportOrderNo"] = have_part["OrderNo"].ToString();
                                            yoyaku_row["SupplyQuantity"] = getqty;
                                            yoyaku_row["DeliveryDate"] = have_part["DeliveryDate"].ToString();
                                            yoyaku_row["ArrivalDate"] = have_part["ArrivalDate"].ToString();
                                            yoyaku_row["PartDate"] = need_part["PartDate"].ToString();
                                            YoyakuTable.Rows.Add(yoyaku_row);

                                            have_part.BeginEdit();
                                            have_part["TotalQuantity"] = totalhave;
                                            have_part.EndEdit();
                                            TotalHave.AcceptChanges();
                                        }
                                        else
                                        {
                                            if (totalhave > 0)
                                            {
                                                getqty = totalhave;
                                                totalhave = 0;
                                                totalneed = totalneed - getqty;

                                                // Add to Yoyaku
                                                DataRow yoyaku_row = YoyakuTable.NewRow();
                                                yoyaku_row["OrderNo"] = need_part["OrderNo"].ToString();
                                                yoyaku_row["RobotNo"] = need_part["RobotNo"].ToString();
                                                float robotqty = 0;
                                                if (float.TryParse(need_part["RobotQuantity"].ToString().Trim(), out robotqty))
                                                {
                                                    yoyaku_row["RobotQuantity"] = robotqty;
                                                }
                                                else
                                                {
                                                    yoyaku_row["RobotQuantity"] = 0;
                                                }
                                                yoyaku_row["DeadLine"] = need_part["ArrivalDate"].ToString();
                                                yoyaku_row["PartNo"] = need_part["PartNo"].ToString();
                                                yoyaku_row["NeedQuantity"] = getqty;
                                                int fix = 0;
                                                if (Int32.TryParse(need_part["FixedDate"].ToString(), out fix))
                                                {
                                                    yoyaku_row["FixedDate"] = fix;
                                                }
                                                else
                                                {
                                                    yoyaku_row["FixedDate"] = 0;
                                                }
                                                yoyaku_row["ImportOrderNo"] = have_part["OrderNo"].ToString();
                                                yoyaku_row["SupplyQuantity"] = getqty;
                                                yoyaku_row["DeliveryDate"] = have_part["DeliveryDate"].ToString();
                                                yoyaku_row["ArrivalDate"] = have_part["ArrivalDate"].ToString();
                                                yoyaku_row["PartDate"] = need_part["PartDate"].ToString();
                                                YoyakuTable.Rows.Add(yoyaku_row);

                                                have_part.BeginEdit();
                                                have_part["TotalQuantity"] = totalhave;
                                                have_part.EndEdit();
                                                TotalHave.AcceptChanges();
                                            }
                                        }
                                    } // No need quantity to calculate
                                } // End of get have quantity

                                if (totalneed > 0)
                                {
                                    //There are some need quantity have to order

                                    // Add to Yoyaku
                                    DataRow yoyaku_row = YoyakuTable.NewRow();
                                    yoyaku_row["OrderNo"] = need_part["OrderNo"].ToString();
                                    yoyaku_row["RobotNo"] = need_part["RobotNo"].ToString();
                                    float robotqty = 0;
                                    if (float.TryParse(need_part["RobotQuantity"].ToString().Trim(), out robotqty))
                                    {
                                        yoyaku_row["RobotQuantity"] = robotqty;
                                    }
                                    else
                                    {
                                        yoyaku_row["RobotQuantity"] = 0;
                                    }
                                    yoyaku_row["DeadLine"] = need_part["ArrivalDate"].ToString();
                                    yoyaku_row["PartNo"] = need_part["PartNo"].ToString();
                                    yoyaku_row["NeedQuantity"] = totalneed;
                                    int fix = 0;
                                    if (Int32.TryParse(need_part["FixedDate"].ToString(), out fix))
                                    {
                                        yoyaku_row["FixedDate"] = fix;
                                    }
                                    else
                                    {
                                        yoyaku_row["FixedDate"] = 0;
                                    }
                                    yoyaku_row["SupplyQuantity"] = 0;
                                    yoyaku_row["PartDate"] = need_part["PartDate"].ToString();
                                    YoyakuTable.Rows.Add(yoyaku_row);
                                }
                            }
                            else
                            {
                                //There are no have quantity for this partno

                                // Add to Yoyaku
                                DataRow yoyaku_row = YoyakuTable.NewRow();
                                yoyaku_row["OrderNo"] = need_part["OrderNo"].ToString();
                                yoyaku_row["RobotNo"] = need_part["RobotNo"].ToString();
                                float robotqty = 0;
                                if (float.TryParse(need_part["RobotQuantity"].ToString().Trim(), out robotqty))
                                {
                                    yoyaku_row["RobotQuantity"] = robotqty;
                                }
                                else
                                {
                                    yoyaku_row["RobotQuantity"] = 0;
                                }
                                yoyaku_row["DeadLine"] = need_part["ArrivalDate"].ToString();
                                yoyaku_row["PartNo"] = need_part["PartNo"].ToString();
                                yoyaku_row["NeedQuantity"] = totalneed;
                                int fix = 0;
                                if (Int32.TryParse(need_part["FixedDate"].ToString(), out fix))
                                {
                                    yoyaku_row["FixedDate"] = fix;
                                }
                                else
                                {
                                    yoyaku_row["FixedDate"] = 0;
                                }
                                yoyaku_row["SupplyQuantity"] = 0;
                                yoyaku_row["PartLine"] = need_part["PartDate"].ToString();
                                YoyakuTable.Rows.Add(yoyaku_row);
                            }
                        }
                    }
                    ModuleWaitDialog.CloseWaitDialog();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Calculate yoyaku error: " + ex.Message);
                    ModuleWaitDialog.CloseWaitDialog();
                }

                // Write Yoyaku to database
                
                try
                {
                    ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
                    int totalyoyaku = YoyakuTable.Rows.Count;
                    int yoyakuindex = 1;
                    if (YoyakuTable.Rows.Count > 0)
                    {   
                        myConnection.Open();
                        SqlCommand sql_cmd;
                        foreach (DataRow yoyaku in YoyakuTable.Rows)
                        {
                            ModuleWaitDialog.SetWaitDialogCaption("Saving Yoyaku " + yoyakuindex + " of " + totalyoyaku + "...");
                            yoyakuindex++;
                            float robotqty, needqty, supplyqty;
                            int fixdate;
                            if (!float.TryParse(yoyaku["RobotQuantity"].ToString(), out robotqty))
                            {
                                robotqty = 0;
                            }
                            if (!float.TryParse(yoyaku["NeedQuantity"].ToString(), out needqty))
                            {
                                needqty = 0;
                            }
                            if (!Int32.TryParse(yoyaku["FixedDate"].ToString(), out fixdate))
                            {
                                fixdate = 0;
                            }
                            if (!float.TryParse(yoyaku["SupplyQuantity"].ToString(), out supplyqty))
                            {
                                supplyqty = 0;
                            }
                            sql_cmd = myConnection.CreateCommand();
                            sql_cmd.CommandText = "INSERT INTO [231_Yoyaku] ([PlanNo], [RobotNo], [RobotQuantity], [DeadLine],"
                                + " [RRCPartNo], [NeedQuantity], [FixedDate], [ImportOrderNo], [SupplyQuantity], [DeliveryDate],"
                                + " [ArrivalDate], PartDate)"
                                + " VALUES ("
                                + " '" + yoyaku["OrderNo"].ToString() + "',"
                                + " N'" + yoyaku["RobotNo"].ToString() + "',"
                                + " " + robotqty + ","
                                + " '" + yoyaku["DeadLine"].ToString() +"',"
                                + " N'" + yoyaku["PartNo"].ToString() + "',"
                                + " " + needqty + ","
                                + " " + fixdate + ","
                                + " '" + yoyaku["ImportOrderNo"].ToString() + "',"
                                + " " + supplyqty + ","
                                + " '" + yoyaku["DeliveryDate"].ToString() + "',"
                                + " '" + yoyaku["ArrivalDate"].ToString() + "',"
                                + " '" + yoyaku["PartDate"].ToString() + "')";
                            sql_cmd.ExecuteNonQuery();
                            sql_cmd.Dispose();
                        }
                        myConnection.Close();
                    }
                    ModuleWaitDialog.CloseWaitDialog();                    
                } 
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    ModuleWaitDialog.CloseWaitDialog();
                    MessageBox.Show("Cannot save Yoyaku. Please try again: " + ex.Message);
                }
                
                MessageBox.Show("Yoyaku finished");
            }
            else
            {
                MessageBox.Show("Cannot Load data to calculate. Please try gain");
            }
            
        }

        private void ShowResult()
        {
            showYoyaku();
        }

        private void buttonShortage_Click(object sender, EventArgs e)
        {
            showShortage();
        }

        private void showYoyaku()
        {
            this.state = 1;
            this.labelControl1.Text = "Yoyaku";
            this.buttonBack.Visible = true;
            this.buttonExportExcel.Visible = true;
            this.buttonLate.Visible = true;
            this.buttonShortage.Visible = true;
            this.buttonYoyaku.Visible = true;
            this.buttonRemain.Visible = true;

            this.buttonCalculate.Visible = false;
            this.buttonGetNew.Visible = false;
            this.buttonHave.Visible = false;
            this.buttonRefresh.Visible = false;

            this.labelControl2.Visible = false;
            this.addDay.Visible = false;


            DataSet yoyakuDataSet = new DataSet();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT [PlanNo], [RobotNo], [RobotQuantity], [DeadLine], [RRCPartNo], [NeedQuantity], [FixedDate],"
                + " [ImportOrderNo], [SupplyQuantity], [DeliveryDate], [ArrivalDate], PartDate"
                + " FROM [231_Yoyaku]";
            yoyakuDataSet = mySQLManagement.SQL_Query(sql);
            if (yoyakuDataSet != null)
            {
                this.gridYoyaku.DataSource = yoyakuDataSet.Tables[0];
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("PlanNo", typeof(string));
                newTable.Columns.Add("RobotNo", typeof(string));
                newTable.Columns.Add("RobotQuantity", typeof(float));
                newTable.Columns.Add("DeadLine", typeof(string));
                newTable.Columns.Add("RRCPartNo", typeof(string));
                newTable.Columns.Add("NeedQuantity", typeof(float));
                newTable.Columns.Add("FixedDate", typeof(bool));
                newTable.Columns.Add("ImportOrderNo", typeof(string));
                newTable.Columns.Add("SupplyQuantity", typeof(float));
                newTable.Columns.Add("DeliveryDate", typeof(string));
                newTable.Columns.Add("ArrivalDate", typeof(string));
                newTable.Columns.Add("PartDate", typeof(string));
                this.gridYoyaku.DataSource = newTable;
            }
            Yoyaku.Columns[0].Caption = "Plan No";
            Yoyaku.Columns[1].Caption = "Robot No";
            Yoyaku.Columns[2].Caption = "Robot Quantity";
            Yoyaku.Columns[3].Caption = "DeadLine";
            Yoyaku.Columns[4].Caption = "RRC Part No";
            Yoyaku.Columns[5].Caption = "Need Quantity";
            Yoyaku.Columns[6].Caption = "FixedDate";
            Yoyaku.Columns[7].Caption = "Import Order No";
            Yoyaku.Columns[8].Caption = "Supply Quantity";
            Yoyaku.Columns[9].Caption = "Delivery Date";
            Yoyaku.Columns[10].Caption = "Arrival Date";
            Yoyaku.Columns[11].Caption = "Part Date";
            Yoyaku.BestFitColumns();

            this.gridYoyaku.BringToFront();
        }

        private void showShortage()
        {
            this.state = 2;
            this.labelControl1.Text = "Shortage";
            this.buttonBack.Visible = true;
            this.buttonExportExcel.Visible = true;
            this.buttonLate.Visible = true;
            this.buttonShortage.Visible = true;
            this.buttonYoyaku.Visible = true;
            this.buttonRemain.Visible = true;

            this.buttonCalculate.Visible = false;
            this.buttonGetNew.Visible = false;
            this.buttonHave.Visible = false;
            this.buttonRefresh.Visible = false;

            this.labelControl2.Visible = false;
            this.addDay.Visible = false;
            

            DataSet shortageDataSet = new DataSet();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT t1.[RRCPartNo], t1.[PARTNAME], t2.Company, t1.[NeedQuantity], t1.[MOQ], t1.[MarumeQuantity],"
                + " t1.[OrderQuantity], t1.[Deadline]"
                + " FROM [VF5Shortage2] AS t1 LEFT JOIN"
                + " [222_Supplier] AS t2 ON t1.[SUPPLIERID] COLLATE Vietnamese_CI_AS = t2.SupplierID";
            shortageDataSet = mySQLManagement.SQL_Query(sql);
            if (shortageDataSet != null)
            {
                this.gridShortTage.DataSource = shortageDataSet.Tables[0];
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("PartNo", typeof(string));
                newTable.Columns.Add("PartName", typeof(string));
                newTable.Columns.Add("Supplier", typeof(string));
                newTable.Columns.Add("NeedQuantity", typeof(float));
                newTable.Columns.Add("MOQ", typeof(float));
                newTable.Columns.Add("Marume", typeof(float));
                newTable.Columns.Add("FinalOrderQuantity", typeof(float));
                newTable.Columns.Add("Deadline", typeof(string));
                this.gridShortTage.DataSource = newTable;
            }
            ShortTage.Columns[0].Caption = "PartNo";
            ShortTage.Columns[1].Caption = "Part Name";
            ShortTage.Columns[2].Caption = "Supplier";
            ShortTage.Columns[3].Caption = "Need Quantity";
            ShortTage.Columns[4].Caption = "MOQ";
            ShortTage.Columns[5].Caption = "Marume";
            ShortTage.Columns[6].Caption = "Final Order Quantity";
            ShortTage.Columns[7].Caption = "Deadline";
            ShortTage.BestFitColumns();

            this.gridShortTage.BringToFront();
        }

        private void showLate()
        {
            this.state = 3;
            this.labelControl1.Text = "Late Final";
            this.buttonBack.Visible = true;
            this.buttonExportExcel.Visible = true;
            this.buttonLate.Visible = true;
            this.buttonShortage.Visible = true;
            this.buttonYoyaku.Visible = true;
            this.buttonRemain.Visible = true;

            this.buttonCalculate.Visible = false;
            this.buttonGetNew.Visible = false;
            this.buttonHave.Visible = false;
            this.buttonRefresh.Visible = false;

            this.labelControl2.Visible = false;
            this.addDay.Visible = false;

            DataSet lateDataSet = new DataSet();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT [PlanNo], [RobotNo], [RobotQuantity], [DeadLine], [RRCPartNo],"
                + " [NeedQuantity], [FixedDate], [ImportOrderNo], [SupplyQuantity], [DeliveryDate], [ArrivalDate], [PartDate]"
                + " FROM [VF5LateFinal]";
            lateDataSet = mySQLManagement.SQL_Query(sql);
            if (lateDataSet != null)
            {
                this.gridLateFinal.DataSource = lateDataSet.Tables[0];
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("PlanNo", typeof(string));
                newTable.Columns.Add("RobotNo", typeof(string));
                newTable.Columns.Add("RobotQuantity", typeof(float));
                newTable.Columns.Add("DeadLine", typeof(string));
                newTable.Columns.Add("RRCPartNo", typeof(string));
                newTable.Columns.Add("NeedQuantity", typeof(float));
                newTable.Columns.Add("FixedDate", typeof(bool));
                newTable.Columns.Add("ImportOrderNo", typeof(string));
                newTable.Columns.Add("SupplyQuantity", typeof(float));
                newTable.Columns.Add("DeliveryDate", typeof(string));
                newTable.Columns.Add("ArrivalDate", typeof(string));
                newTable.Columns.Add("PartDate", typeof(string));
                this.gridLateFinal.DataSource = newTable;
            }
            LateFinal.Columns[0].Caption = "Plan No";
            LateFinal.Columns[1].Caption = "Robot No";
            LateFinal.Columns[2].Caption = "Robot Quantity";
            LateFinal.Columns[3].Caption = "Deadline";
            LateFinal.Columns[4].Caption = "RRC Part No";
            LateFinal.Columns[5].Caption = "Need Quantity";
            LateFinal.Columns[6].Caption = "Fixed Date";
            LateFinal.Columns[7].Caption = "Import Order No";
            LateFinal.Columns[8].Caption = "Supply Quantity";
            LateFinal.Columns[9].Caption = "Delivery Date";
            LateFinal.Columns[10].Caption = "Arrival Date";
            LateFinal.Columns[11].Caption = "Part Date";
            LateFinal.BestFitColumns();

            this.gridLateFinal.BringToFront();

        }

        private void showRemain()
        {
            this.state = 4;
            this.labelControl1.Text = "Remain Quantity";
            this.buttonBack.Visible = true;
            this.buttonExportExcel.Visible = true;
            this.buttonLate.Visible = true;
            this.buttonShortage.Visible = true;
            this.buttonYoyaku.Visible = true;
            this.buttonRemain.Visible = true;

            this.buttonCalculate.Visible = false;
            this.buttonGetNew.Visible = false;
            this.buttonHave.Visible = false;
            this.buttonRefresh.Visible = false;

            this.labelControl2.Visible = false;
            this.addDay.Visible = false;

            DataSet remainDataSet = new DataSet();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT [ORDERNO], [PARTNO], [DELIVERYDATE], [TOTALQUANTITY], [UseZaiko], [RemainQuantity]"
                + " FROM [VF5Remain]";
            remainDataSet = mySQLManagement.SQL_Query(sql);
            if (remainDataSet != null)
            {
                this.gridRemain.DataSource = remainDataSet.Tables[0];
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ORDERNO", typeof(string));
                newTable.Columns.Add("PARTNO", typeof(string));
                newTable.Columns.Add("DELIVERYDATE", typeof(float));
                newTable.Columns.Add("TOTALQUANTITY", typeof(string));
                newTable.Columns.Add("UseZaiko", typeof(string));
                newTable.Columns.Add("RemainQuantity", typeof(float));
                this.gridRemain.DataSource = newTable;
            }
            Remain.Columns[0].Caption = "Import Order";
            Remain.Columns[1].Caption = "RRC Part No";
            Remain.Columns[2].Caption = "Delivery Date";
            Remain.Columns[3].Caption = "Import Quantity";
            Remain.Columns[4].Caption = "Use Zaiko";
            Remain.Columns[5].Caption = "Remain Quantity";
            Remain.BestFitColumns();

            this.gridRemain.BringToFront();

        }

        private void buttonBack_Click(object sender, EventArgs e)
        {
            this.state = 0;
            this.labelControl1.Text = "F5 Material Calculation Plan";
            LoadData();
            this.gridF5YoyakuPlan.BringToFront();
            this.buttonBack.Visible = false;
            this.buttonExportExcel.Visible = false;
            this.buttonLate.Visible = false;
            this.buttonShortage.Visible = false;
            this.buttonYoyaku.Visible = false;
            this.buttonRemain.Visible = false;

            this.buttonCalculate.Visible = true;
            this.buttonGetNew.Visible = true;
            this.buttonHave.Visible = true;
            this.buttonRefresh.Visible = true;

            this.labelControl2.Visible = true;
            this.addDay.Visible = true;

        }

        private void buttonLate_Click(object sender, EventArgs e)
        {
            showLate();
        }

        private void buttonExportExcel_Click(object sender, EventArgs e)
        {
            Cursor currentCursor;
            switch (this.state)
            {
                case 1:
                    currentCursor = Cursor.Current;
                    Cursor.Current = Cursors.WaitCursor;
                    try
                    {
                        SaveFileDialog saveFDlg = new SaveFileDialog();
                        saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                        saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                        saveFDlg.Title = "Save As";
                        //saveFDlg.CheckFileExists = true;
                        saveFDlg.FileName = "Yoyaku.xlsx";
                        if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                        {
                            if (!string.IsNullOrEmpty(saveFDlg.FileName))
                            {
                                Yoyaku.OptionsPrint.PrintDetails = true;
                                Yoyaku.OptionsPrint.ExpandAllDetails = true;
                                Yoyaku.OptionsPrint.AutoWidth = true;
                                Yoyaku.ExportToXlsx(saveFDlg.FileName);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Warning!");
                    }
                    finally
                    {
                        Cursor.Current = currentCursor;
                    }
                    break;
                case 2:
                    currentCursor = Cursor.Current;
                    Cursor.Current = Cursors.WaitCursor;
                    try
                    {
                        SaveFileDialog saveFDlg = new SaveFileDialog();
                        saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                        saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                        saveFDlg.Title = "Save As";
                        //saveFDlg.CheckFileExists = true;
                        saveFDlg.FileName = "ShortTage.xlsx";
                        if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                        {
                            if (!string.IsNullOrEmpty(saveFDlg.FileName))
                            {
                                ShortTage.OptionsPrint.PrintDetails = true;
                                ShortTage.OptionsPrint.ExpandAllDetails = true;
                                ShortTage.OptionsPrint.AutoWidth = true;
                                ShortTage.ExportToXlsx(saveFDlg.FileName);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Warning!");
                    }
                    finally
                    {
                        Cursor.Current = currentCursor;
                    }
                    break;
                case 3:
                    currentCursor = Cursor.Current;
                    Cursor.Current = Cursors.WaitCursor;
                    try
                    {
                        SaveFileDialog saveFDlg = new SaveFileDialog();
                        saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                        saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                        saveFDlg.Title = "Save As";
                        //saveFDlg.CheckFileExists = true;
                        saveFDlg.FileName = "LateFinal.xlsx";
                        if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                        {
                            if (!string.IsNullOrEmpty(saveFDlg.FileName))
                            {
                                LateFinal.OptionsPrint.PrintDetails = true;
                                LateFinal.OptionsPrint.ExpandAllDetails = true;
                                LateFinal.OptionsPrint.AutoWidth = true;
                                LateFinal.ExportToXlsx(saveFDlg.FileName);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Warning!");
                    }
                    finally
                    {
                        Cursor.Current = currentCursor;
                    }
                    break;
                case 4:
                    currentCursor = Cursor.Current;
                    Cursor.Current = Cursors.WaitCursor;
                    try
                    {
                        SaveFileDialog saveFDlg = new SaveFileDialog();
                        saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                        saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                        saveFDlg.Title = "Save As";
                        //saveFDlg.CheckFileExists = true;
                        saveFDlg.FileName = "Remain.xlsx";
                        if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                        {
                            if (!string.IsNullOrEmpty(saveFDlg.FileName))
                            {
                                Remain.OptionsPrint.PrintDetails = true;
                                Remain.OptionsPrint.ExpandAllDetails = true;
                                Remain.OptionsPrint.AutoWidth = true;
                                Remain.ExportToXlsx(saveFDlg.FileName);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Warning!");
                    }
                    finally
                    {
                        Cursor.Current = currentCursor;
                    }
                    break;
            }
        }

        private void buttonAddDate_Click(object sender, EventArgs e)
        {
            int addday = 0;
            if (string.IsNullOrEmpty(this.addDay.Text))
            {
                addday = 15;
            }
            else
            {
                if (Int32.TryParse(this.addDay.Text, out addday))
                {
                    try
                    {
                        myConnection.Open();
                        SqlCommand sql_cmd = myConnection.CreateCommand();
                        sql_cmd.CommandText = "UPDATE [231_F5YoyakuPlan]"
                            + " SET PartDate=CONVERT(NVARCHAR(20), DATEADD(dd," + (-1 * addday).ToString() + ",CONVERT(DATETIME, [ArrivalDate], 112)), 112)";
                        sql_cmd.ExecuteNonQuery();
                        sql_cmd.Dispose();
                        myConnection.Close();
                        MessageBox.Show("Add days successfullly!");
                    }
                    catch
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show("Cannot Add Date to Part Date");
                    }
                }
                else
                {
                    MessageBox.Show("Invalid Add Days number.");
                }
            }
            LoadData();
        }

        private void buttonYoyaku_Click(object sender, EventArgs e)
        {
            showYoyaku();
        }

        private void buttonRemain_Click(object sender, EventArgs e)
        {
            showRemain();
        }
    }
}
